
SET QUOTED_IDENTIFIER ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO

alter table dbo.AEUser
add UserSID varchar(255) NULL
go

/**************** AEUser table constraints ****************/
if not exists(select * from sysconstraints where id = object_id('dbo.AEUser') and object_name(constid) = 'PK_AEUser' )	
begin
	-- check if there are any foreign keys referencing this table 
	if exists(select constid from sysreferences where rkeyid = object_id('dbo.AEUser'))
	begin
		declare @constid int, @fkeyid int, @SQL nvarchar(255)
		declare fkeys cursor local read_only for
		select constid, fkeyid from sysreferences where rkeyid = object_id('dbo.AEUser')
		open fkeys
		fetch next from fkeys into @constid, @fkeyid
		while @@FETCH_STATUS = 0 		
		begin
			set @SQL = 'alter table ' + object_name(@fkeyid) + ' drop constraint ' + object_name(@constid)
			exec sp_executesql @SQL
			fetch next from fkeys into @constid, @fkeyid
		end
		close fkeys
		deallocate fkeys
	end

	--re-create this index as a Primary Key constraint
	drop index dbo.AEUser.PK_AEUser

	ALTER TABLE dbo.AEUser ADD CONSTRAINT
		PK_AEUser PRIMARY KEY CLUSTERED 
		(
		UserId
		) ON [PRIMARY]
end
go

/**************** BlobTable constraints ****************/
if not exists(select * from sysconstraints where id = object_id('dbo.BlobTable') and object_name(constid) = 'PK_BlobTable' )	
begin
	-- check if there are any foreign keys referencing this table 
	if exists(select constid from sysreferences where rkeyid = object_id('dbo.BlobTable'))
	begin
		declare @constid int, @fkeyid int, @SQL nvarchar(255)
		declare fkeys cursor local read_only for
		select constid, fkeyid from sysreferences where rkeyid = object_id('dbo.BlobTable')
		open fkeys
		fetch next from fkeys into @constid, @fkeyid
		while @@FETCH_STATUS = 0 		
		begin
			set @SQL = 'alter table ' + object_name(@fkeyid) + ' drop constraint ' + object_name(@constid)
			exec sp_executesql @SQL
			fetch next from fkeys into @constid, @fkeyid
		end
		close fkeys
		deallocate fkeys
	end

	--re-create this index as a Primary Key constraint
	drop index dbo.BlobTable.PK_BlobTable

	ALTER TABLE dbo.BlobTable ADD CONSTRAINT
		PK_BlobTable PRIMARY KEY CLUSTERED 
		(
		BlobId
		) ON [PRIMARY]
end
go

/**************** Node table constraints ****************/
if not exists(select * from sysconstraints where id = object_id('dbo.Node') and object_name(constid) = 'PK_Node' )	
begin
	-- check if there are any foreign keys referencing this table 
	if exists(select constid from sysreferences where rkeyid = object_id('dbo.Node'))
	begin
		declare @constid int, @fkeyid int, @SQL nvarchar(255)
		declare fkeys cursor local read_only for
		select constid, fkeyid from sysreferences where rkeyid = object_id('dbo.Node')
		open fkeys
		fetch next from fkeys into @constid, @fkeyid
		while @@FETCH_STATUS = 0 		
		begin
			set @SQL = 'alter table ' + object_name(@fkeyid) + ' drop constraint ' + object_name(@constid)
			exec sp_executesql @SQL
			fetch next from fkeys into @constid, @fkeyid
		end
		close fkeys
		deallocate fkeys
	end

	--re-create this index as a Primary Key constraint
	drop index dbo.Node.PK_Node

	ALTER TABLE dbo.Node ADD CONSTRAINT
		PK_Node PRIMARY KEY NONCLUSTERED 
		(
		Id
		) ON [PRIMARY]
end

if not exists(select constid from sysforeignkeys 
				where fkeyid = object_id('dbo.Node') and 
				object_name(constid)= 'FK_Node_CreatedByUserID')
begin
	ALTER TABLE dbo.Node ADD CONSTRAINT
		FK_Node_CreatedByUserID FOREIGN KEY
		(
		CreatedByUserID
		) REFERENCES dbo.AEUser
		(
		UserId
		)
end
go


if not exists(select constid from sysforeignkeys 
				where fkeyid = object_id('dbo.Node') and 
				object_name(constid)= 'FK_Node_ModifiedByUserID')
begin
	ALTER TABLE dbo.Node ADD CONSTRAINT
		FK_Node_ModifiedByUserID FOREIGN KEY
		(
		ModifiedByUserID
		) REFERENCES dbo.AEUser
		(
		UserId
		)		
end
go

if not exists(select constid from sysforeignkeys 
				where fkeyid = object_id('dbo.Node') and 
				object_name(constid)= 'FK_Node_ApprovedByUserID')
begin
	ALTER TABLE dbo.Node ADD CONSTRAINT
		FK_Node_ApprovedByUserID FOREIGN KEY
		(
		ApprovedByUserID
		) REFERENCES dbo.AEUser
		(
		UserId
		)
end
go

if not exists(select constid from sysforeignkeys 
				where fkeyid = object_id('node') and 
				object_name(constid)= 'FK_Node_DeletedByUserID')
begin
	ALTER TABLE dbo.Node ADD CONSTRAINT
		FK_Node_DeletedByUserID FOREIGN KEY
		(
		DeletedByUserID
		) REFERENCES dbo.AEUser
		(
		UserId
		)
end
go

if not exists(select constid from sysforeignkeys 
				where fkeyid = object_id('node') and 
				object_name(constid)= 'FK_Node_OwnerID')
begin
	ALTER TABLE dbo.Node ADD CONSTRAINT
		FK_Node_OwnerID FOREIGN KEY
		(
		OwnerID
		) REFERENCES dbo.AEUser
		(
		UserId
		)
end
go

/**************** NodeLock table constraints ****************/
if not exists(select * from sysconstraints where id = object_id('dbo.NodeLock') and object_name(constid) = 'PK_NodeLock' )	
begin
	--re-create this index as a Primary Key constraint
	drop index dbo.NodeLock.PK_NodeLock

	ALTER TABLE dbo.NodeLock ADD CONSTRAINT
		PK_NodeLock PRIMARY KEY CLUSTERED 
		(
		NodeId
		) ON [PRIMARY]	
end


if not exists(select constid from sysforeignkeys 
				where fkeyid = object_id('dbo.NodeLock') and 
				object_name(constid)= 'FK_NodeLock_Node')
begin
	ALTER TABLE dbo.NodeLock ADD CONSTRAINT
		FK_NodeLock_Node FOREIGN KEY
		(
		NodeId
		) REFERENCES dbo.Node
		(
		Id
		)	
end
GO

if not exists(select constid from sysforeignkeys 
				where fkeyid = object_id('dbo.NodeLock') and 
				object_name(constid)= 'FK_NodeLock_NewApprovedByUserID')
begin
	ALTER TABLE dbo.NodeLock ADD CONSTRAINT
		FK_NodeLock_NewApprovedByUserID FOREIGN KEY
		(
		NewApprovedByUserID
		) REFERENCES dbo.AEUser
		(
		UserId
		)
end
go

if not exists(select constid from sysforeignkeys 
				where fkeyid = object_id('dbo.NodeLock') and 
				object_name(constid)= 'FK_NodeLock_NewOwnerID')
begin
	ALTER TABLE dbo.NodeLock ADD CONSTRAINT
		FK_NodeLock_NewOwnerID FOREIGN KEY
		(
		NewOwnerID
		) REFERENCES dbo.AEUser
		(
		UserId
		)
end
go


/**************** UserRoleMember constraints ****************/
if not exists(select * from sysconstraints where id = object_id('dbo.UserRoleMember') and object_name(constid) = 'PK_UserRoleMember' )	
begin
	--re-create this index as a Primary Key constraint
	drop index dbo.UserRoleMember.PK_UserRoleMember

	ALTER TABLE dbo.UserRoleMember ADD CONSTRAINT
		PK_UserRoleMember PRIMARY KEY CLUSTERED 
		(
		NodeId,
		UserId
		) ON [PRIMARY]
end

if not exists(select constid from sysforeignkeys 
				where fkeyid = object_id('dbo.UserRoleMember') and 
				object_name(constid)= 'FK_UserRoleMember_Node')
begin
	ALTER TABLE dbo.UserRoleMember ADD CONSTRAINT
		FK_UserRoleMember_Node FOREIGN KEY
		(
		NodeId
		) REFERENCES dbo.Node
		(
		Id
		)
end
go

if not exists(select constid from sysforeignkeys 
				where fkeyid = object_id('dbo.UserRoleMember') and 
				object_name(constid)= 'FK_UserRoleMember_AEUser')
begin
	ALTER TABLE dbo.UserRoleMember ADD CONSTRAINT
		FK_UserRoleMember_AEUser FOREIGN KEY
		(
		UserId
		) REFERENCES dbo.AEUser
		(
		UserId
		)
end
go

/**************** NodeResource constraints ****************/
if not exists(select * from sysconstraints where id = object_id('dbo.NodeResource') and object_name(constid) = 'PK_NodeResource' )	
begin
	--re-create this index as a Primary Key constraint
	drop index dbo.NodeResource.PK_NodeResource

	ALTER TABLE dbo.NodeResource ADD CONSTRAINT
		PK_NodeResource PRIMARY KEY CLUSTERED 
		(
		NodeId,
		Id
		) ON [PRIMARY]
end

if not exists(select constid from sysforeignkeys 
				where fkeyid = object_id('dbo.NodeResource') and 
				object_name(constid)= 'FK_NodeResource_Node')
begin
	ALTER TABLE dbo.NodeResource ADD CONSTRAINT
		FK_NodeResource_Node FOREIGN KEY
		(
		NodeId
		) REFERENCES dbo.Node
		(
		Id
		)
end
go

if not exists(select constid from sysforeignkeys 
				where fkeyid = object_id('dbo.NodeResource') and 
				object_name(constid)= 'FK_NodeResource_BlobTable')
begin
	ALTER TABLE dbo.NodeResource ADD CONSTRAINT
		FK_NodeResource_BlobTable FOREIGN KEY
		(
		ResourceBlobId
		) REFERENCES dbo.BlobTable
		(
		BlobId
		)
end
go

if not exists(select constid from sysforeignkeys 
				where fkeyid = object_id('dbo.NodeResource') and 
				object_name(constid)= 'FK_NewNodeResource_BlobTable')
begin
	ALTER TABLE dbo.NodeResource ADD CONSTRAINT
		FK_NewNodeResource_BlobTable FOREIGN KEY
		(
		NewResourceBlobId
		) REFERENCES dbo.BlobTable
		(
		BlobId
		)
end
GO


/**************** NodeProperty constraints ****************/
if not exists(select * from sysconstraints where id = object_id('dbo.NodeProperty') and object_name(constid) = 'PK_NodeProperty' )	
begin
	--re-create this index as a Primary Key constraint
	drop index dbo.NodeProperty.PK_NodeProperty

	ALTER TABLE dbo.NodeProperty ADD CONSTRAINT
		PK_NodeProperty PRIMARY KEY CLUSTERED 
		(
		NodeId,
		Id
		) ON [PRIMARY]
end

if not exists(select constid from sysforeignkeys 
				where fkeyid = object_id('dbo.NodeProperty') and 
				object_name(constid)= 'FK_NodeProperty_Node')
begin
	ALTER TABLE dbo.NodeProperty ADD CONSTRAINT
		FK_NodeProperty_Node FOREIGN KEY
		(
		NodeId
		) REFERENCES dbo.Node
		(
		Id
		)
end
go


/**************** LayoutTemplateProperty constraints ****************/
if not exists(select * from sysconstraints where id = object_id('dbo.LayoutTemplateProperty') and 
object_name(constid) = 'PK_LayoutTemplateProperty' )	
begin
	--re-create this index as a Primary Key constraint
	drop index dbo.LayoutTemplateProperty.PK_LayoutTemplateProperty

	ALTER TABLE dbo.LayoutTemplateProperty ADD CONSTRAINT
		PK_LayoutTemplateProperty PRIMARY KEY CLUSTERED 
		(
		NodeId,
		Id
		) ON [PRIMARY]
end

if not exists(select constid from sysforeignkeys 
				where fkeyid = object_id('dbo.LayoutTemplateProperty') and 
				object_name(constid)= 'FK_LayoutTemplateProperty_Node')
begin
	ALTER TABLE dbo.LayoutTemplateProperty ADD CONSTRAINT
		FK_LayoutTemplateProperty_Node FOREIGN KEY
		(
		NodeId
		) REFERENCES dbo.Node
		(
		Id
		)
end
go


/**************** GalleryControl constraints ****************/
if not exists(select * from sysconstraints where id = object_id('dbo.GalleryControl') and 
object_name(constid) = 'PK_GalleryControl' )	
begin
	--re-create this index as a Primary Key constraint
	drop index dbo.GalleryControl.PK_GalleryControl

	ALTER TABLE dbo.GalleryControl ADD CONSTRAINT
		PK_GalleryControl PRIMARY KEY CLUSTERED 
		(
		NodeId,
		Id
		) ON [PRIMARY]
end

if not exists(select constid from sysforeignkeys 
				where fkeyid = object_id('dbo.GalleryControl') and 
				object_name(constid)= 'FK_GalleryControl_Node')
begin
	ALTER TABLE dbo.GalleryControl ADD CONSTRAINT
		FK_GalleryControl_Node FOREIGN KEY
		(
		NodeId
		) REFERENCES dbo.Node
		(
		Id
		)
end
go


/**************** NodeControl constraints ****************/
if not exists(select * from sysconstraints where id = object_id('dbo.NodeControl') and object_name(constid) = 'PK_NodeControl' )	
begin
	-- check if there are any foreign keys referencing this table 
	if exists(select constid from sysreferences where rkeyid = object_id('dbo.NodeControl'))
	begin
		declare @constid int, @fkeyid int, @SQL nvarchar(255)
		declare fkeys cursor local read_only for
		select constid, fkeyid from sysreferences where rkeyid = object_id('dbo.NodeControl')
		open fkeys
		fetch next from fkeys into @constid, @fkeyid
		while @@FETCH_STATUS = 0 		
		begin
			set @SQL = 'alter table ' + object_name(@fkeyid) + ' drop constraint ' + object_name(@constid)
			exec sp_executesql @SQL
			fetch next from fkeys into @constid, @fkeyid
		end
		close fkeys
		deallocate fkeys
	end

	--re-create this index as a Primary Key constraint
	drop index dbo.NodeControl.PK_NodeControl

	ALTER TABLE dbo.NodeControl ADD CONSTRAINT
		PK_NodeControl PRIMARY KEY CLUSTERED 
		(
		NodeId,
		Id
		) ON [PRIMARY]	
end

if not exists(select constid from sysforeignkeys 
				where fkeyid = object_id('dbo.NodeControl') and 
				object_name(constid)= 'FK_NodeControl_Node')
begin
	ALTER TABLE dbo.NodeControl ADD CONSTRAINT
		FK_NodeControl_Node FOREIGN KEY
		(
		NodeId
		) REFERENCES dbo.Node
		(
		Id
		)
end
go


/**************** ControlProperty constraints ****************/
if not exists(select * from sysconstraints where id = object_id('dbo.ControlProperty') and 
object_name(constid) = 'PK_ControlProperty' )	
begin
	--re-create this index as a Primary Key constraint
	drop index dbo.ControlProperty.PK_ControlProperty

	ALTER TABLE dbo.ControlProperty ADD CONSTRAINT
		PK_ControlProperty PRIMARY KEY CLUSTERED 
		(
		NodeId,
		ControlId,
		Id
		) ON [PRIMARY]
end

if not exists(select constid from sysforeignkeys 
				where fkeyid = object_id('dbo.ControlProperty') and 
				object_name(constid)= 'FK_ControlProperty_NodeControl')
begin
	ALTER TABLE dbo.ControlProperty ADD CONSTRAINT
		FK_ControlProperty_NodeControl FOREIGN KEY
		(
		NodeId,
		ControlId
		) REFERENCES dbo.NodeControl
		(
		NodeId,
		Id
		)
end
go

/**************** NodeLayout constraints ****************/
if not exists(select * from sysconstraints where id = object_id('dbo.NodeLayout') and object_name(constid) = 'PK_NodeLayout' )	
begin
	-- check if there are any foreign keys referencing this table 
	if exists(select constid from sysreferences where rkeyid = object_id('dbo.NodeLayout'))
	begin
		declare @constid int, @fkeyid int, @SQL nvarchar(255)
		declare fkeys cursor local read_only for
		select constid, fkeyid from sysreferences where rkeyid = object_id('dbo.NodeLayout')
		open fkeys
		fetch next from fkeys into @constid, @fkeyid
		while @@FETCH_STATUS = 0 		
		begin
			set @SQL = 'alter table ' + object_name(@fkeyid) + ' drop constraint ' + object_name(@constid)
			exec sp_executesql @SQL
			fetch next from fkeys into @constid, @fkeyid
		end
		close fkeys
		deallocate fkeys
	end

	--re-create this index as a Primary Key constraint
	drop index dbo.NodeLayout.PK_NodeLayout

	ALTER TABLE dbo.NodeLayout ADD CONSTRAINT
		PK_NodeLayout PRIMARY KEY CLUSTERED 
		(
		NodeId,
		Id
		) ON [PRIMARY]
end

if not exists(select constid from sysforeignkeys 
				where fkeyid = object_id('dbo.NodeLayout') and 
				object_name(constid)= 'FK_NodeLayout_Node')
begin
	ALTER TABLE dbo.NodeLayout ADD CONSTRAINT
		FK_NodeLayout_Node FOREIGN KEY
		(
		NodeId
		) REFERENCES dbo.Node
		(
		Id
		)		
end
go

/**************** LayoutProperty constraints ****************/
if not exists(select * from sysconstraints where id = object_id('dbo.LayoutProperty') and 
object_name(constid) = 'PK_LayoutProperty' )	
begin
	--re-create this index as a Primary Key constraint
	drop index dbo.LayoutProperty.PK_LayoutProperty

	ALTER TABLE dbo.LayoutProperty ADD CONSTRAINT
		PK_LayoutProperty PRIMARY KEY CLUSTERED 
		(
		NodeId,
		LayoutId,
		Id
		) ON [PRIMARY]
end

if not exists(select constid from sysforeignkeys 
				where fkeyid = object_id('dbo.LayoutProperty') and 
				object_name(constid)= 'FK_LayoutProperty_NodeLayout')
begin
	ALTER TABLE dbo.LayoutProperty ADD CONSTRAINT
		FK_LayoutProperty_NodeLayout FOREIGN KEY
		(
		NodeId,
		LayoutId
		) REFERENCES dbo.NodeLayout
		(
		NodeId,
		Id
		)
end
go

/**************** NodeRole constraints ****************/
if not exists(select * from sysconstraints where id = object_id('dbo.NodeRole') and 
object_name(constid) = 'PK_NodeRole' )	
begin
	--re-create this index as a Primary Key constraint
	drop index dbo.NodeRole.PK_NodeRole

	ALTER TABLE dbo.NodeRole ADD CONSTRAINT
		PK_NodeRole PRIMARY KEY CLUSTERED 
		(
		NodeId,
		RoleGUID
		) ON [PRIMARY]
end

if not exists(select constid from sysforeignkeys 
				where fkeyid = object_id('dbo.NodeRole') and 
				object_name(constid)= 'FK_NodeRole_Node')
begin
	ALTER TABLE dbo.NodeRole ADD CONSTRAINT
		FK_NodeRole_Node FOREIGN KEY
		(
		NodeId
		) REFERENCES dbo.Node
		(
		Id
		)
end
go

/**************** UserRoleNode constraints ****************/
if not exists(select * from sysconstraints where id = object_id('dbo.UserRoleNode') and 
object_name(constid) = 'PK_UserRoleNode' )	
begin
	--re-create this index as a Primary Key constraint
	drop index dbo.UserRoleNode.PK_UserRoleNode

	ALTER TABLE dbo.UserRoleNode ADD CONSTRAINT
		PK_UserRoleNode PRIMARY KEY CLUSTERED 
		(
		NodeId,
		NodeType
		) ON [PRIMARY]
end

if not exists(select constid from sysforeignkeys 
				where fkeyid = object_id('dbo.UserRoleNode') and 
				object_name(constid)= 'FK_UserRoleNode_Node')
begin
	ALTER TABLE dbo.UserRoleNode ADD CONSTRAINT
		FK_UserRoleNode_Node FOREIGN KEY
		(
		NodeId
		) REFERENCES dbo.Node
		(
		Id
		)
end
go



if exists(select * from sysconstraints where id = object_id('dbo.ChangeTable') and 
object_name(constid) = 'PK_ChangeTable' )	
	ALTER TABLE dbo.ChangeTable drop constraint PK_ChangeTable
else
	drop index dbo.ChangeTable.PK_ChangeTable

drop index ChangeTable.ChangeTable_CommitTime
go

ALTER TABLE dbo.ChangeTable ADD CONSTRAINT
	PK_ChangeTable Primary Key NonClustered
	(
	NodeGUID
	)
Go
CREATE Clustered Index ChangeTable_CommitTime On dbo.ChangeTable
	(
	CommitTime
	)
Go



alter PROCEDURE GetUserID
	(
	@UserName		nvarchar(450), 
	@SID			varchar(255),
	@Alias			nvarchar(450),	
	@AddIfNotFound	tinyint,
	@UserId 		int OUTPUT
	)
	with encryption
as
	set nocount on
	set @UserId = 0
	
	declare @UpdateSID tinyint
	declare @ExistingSID varchar(255)
	set @UpdateSID = 0

	/*first search by username*/
	SELECT @UserId = userid, @ExistingSID = UserSID FROM aeuser WHERE usernameupper = UPPER(@UserName)

	/*see if we can use SID to find the user*/
	if isnull(@UserId, 0) = 0  and  isnull(@SID, '') <> ''
		SELECT @UserId = userid, @ExistingSID = UserSID FROM aeuser WHERE UserSID is not null and UserSID = @SID 

	/*see if we can use Alias*/
	if isnull(@UserId, 0) = 0 and isnull(@Alias, '') <> ''
		SELECT @UserId = userid, @ExistingSID = UserSID FROM aeuser WHERE usernameupper = UPPER(@Alias)

	if isnull(@UserId, 0) = 0
	begin
		if @AddIfNotFound = 1
		begin
			INSERT INTO aeuser (username, UserSID) VALUES (@UserName, nullif(@SID, ''))
			set @UserId = @@IDENTITY
		end
		else
			set @UserId = isnull(@UserId, 0)
	end
	else	--update SID if it's available
		if isnull(@ExistingSID, '') = '' and isnull(@SID, '') <> ''
			update AEUser set UserSID = @SID where UserId = @UserId	


GO


alter procedure PurgeRevisionsByDate
		(
		@RevisionDate datetime
		)
	with encryption
as
/**
	@exception - @RevisionDate is older than the most resent Revision Purge
 */
	declare @LastPurgeDate datetime
	declare @SQL varchar(255)

	set nocount on

	/*check if RevisionDate is older than the last revision purge*/
	select @LastPurgeDate = LastRevisionPurge from utility

	if @LastPurgeDate is not null and @LastPurgeDate >= @RevisionDate 
		return 1	/*RevisionDate is older than the last revision purge*/


	DECLARE @CurrentId as int
	
	DECLARE DeletedItems CURSOR LOCAL READ_ONLY 
	    FOR SELECT Id FROM Node WHERE ArchivedWhen <= @RevisionDate 
		and (type <> 256 or (type = 256 and nodeguid not in (select nodeguid from noderesource where nodeguid is not null) )) 
	
	OPEN DeletedItems
	FETCH NEXT FROM DeletedItems INTO @CurrentId
	
	WHILE @@FETCH_STATUS = 0
	  BEGIN
			DELETE FROM NodeProperty WHERE NodeId = @CurrentId
			DELETE FROM NodeRole WHERE NodeId = @CurrentId
			DELETE FROM NodeResource WHERE NodeId = @CurrentId
			DELETE FROM ControlProperty WHERE NodeId = @CurrentId
			DELETE FROM NodeControl WHERE NodeId = @CurrentId
			DELETE FROM LayoutProperty WHERE NodeId = @CurrentId
			DELETE FROM NodeLayout WHERE NodeId = @CurrentId
			DELETE FROM GalleryControl WHERE NodeId = @CurrentId
			DELETE FROM LayoutTemplateProperty WHERE NodeId = @CurrentId
			DELETE FROM NodeRole WHERE NodeId = @CurrentId
			DELETE FROM UserRoleMember WHERE NodeId = @CurrentId
			DELETE FROM UserRoleNode WHERE NodeId = @CurrentId
			DELETE FROM NodeLock WHERE NodeId = @CurrentId
			DELETE FROM Node WHERE Id = @CurrentId
			
			FETCH NEXT FROM DeletedItems INTO @CurrentId
	  END  

	DECLARE @dateGMT datetime, @dateCurrent datetime
	select @dateCurrent = GETDATE()
	exec GetGMTTime @dateCurrent, @dateGMT out
	
	if @RevisionDate > @dateGMT
		set @RevisionDate = @dateGMT

	if exists (select * from utility )
		update utility set LastRevisionPurge = @RevisionDate
	else
		insert into utility (LastRevisionPurge) values(@RevisionDate)

	close DeletedItems
	deallocate DeletedItems

	return 0			
go


if exists (select NodeId from NodeProperty where PropName = 'LDAPSupport' and PropValue = 'ActiveDirectory')
begin
	delete from nodeproperty where PropName = 'LDAPUserName' or PropName = 'LDAPPassword'
end
go

create procedure GetNextPublishingChangeTime
	(
	@NextChangeTime datetime out
	)
as
	declare @NextEffectiveDate datetime, @NextExpiryDate datetime

	DECLARE @dateGMT datetime, @dateCurrent datetime
	select @dateCurrent = GETDATE()
	exec GetGMTTime @dateCurrent, @dateGMT out

	select @NextEffectiveDate = min(EffectiveDate)
	from node
	where EffectiveDate > @dateGMT and ApprovalStatus = 1

	select @NextExpiryDate = min(ExpiryDate)
	from node
	where ExpiryDate > @dateGMT and ApprovalStatus = 1

	if @NextEffectiveDate <= @NextExpiryDate
		set @NextChangeTime = @NextEffectiveDate
	else if @NextExpiryDate < @NextEffectiveDate 
		set @NextChangeTime = @NextExpiryDate
	else	--one of these values is null
		set @NextChangeTime = isnull(COALESCE(@NextEffectiveDate, @NextExpiryDate ), '3000-01-01 00:00:00.000')

go

GRANT EXECUTE ON GetNextPublishingChangeTime TO Public
go


alter procedure DeleteRecursive
	(
	@DeleteAction int,	
	@SourceNodeGUID char(38),
	@NewParentGUID char(38),
	@UserID	int,
	@UserMachineName nvarchar(50),
	@UserGroupIDs varchar(1000),
	@ArchiveFolderGUID char(38)
	)
	with encryption
as
/*Enum values for @DeleteAction
	0 - Move to Recycle Bin
	1 - Move from Recycle Bin
	2 - Purge from the database
*/

/*Enum values for Node Status
	2 - USER_LOCK_FAILED
	4 - RIGHTS_FAILED
	8 - TEMPLATE_FAILED
*/
	declare @MOVE_TO_RECYCLE_BIN int, @MOVE_FROM_RECYCLE_BIN int, @PURGE int
	set @MOVE_TO_RECYCLE_BIN = 0
	set @MOVE_FROM_RECYCLE_BIN = 1
	set @PURGE = 2

	declare @ACL_FULL_CONTROL varchar(15), @ACL_DELETE_SIMPLE_NODE varchar(15), @ACL_DELETE_OWN_NODE varchar(15)
	set @ACL_FULL_CONTROL = '-2147483648'
	set @ACL_DELETE_SIMPLE_NODE = '16'
	set @ACL_DELETE_OWN_NODE = '67108864'

   declare @level int, @currParentGUID char(38), @currID int, @Type int, @name varchar(250), @IsShortcut tinyint
   declare @Failed tinyint
   declare @SQL nvarchar(1000)
   declare @HasRights int
   declare @IsItem tinyint
   DECLARE @dateGMT datetime, @dateCurrent datetime
   declare @IsSourceNodeDeleted int
  
   set nocount on
   set @Failed = 0
   set @IsItem = 0
   set @Type = 0
   set @HasRights = 0

   /*temporary table that stores nodes being deleted, 
	that is the specified node plus all its children*/
    create table #nodes_to_delete
		(
		seq_no	int IDENTITY,
		NodeLevel int not null,
		Id int not null,
		NodeGUID char(38) not null,
		Type int not null,
		IsShortcut tinyint not null,
		name varchar(250) null
		);

    CREATE Index nodes_to_delete_id On #nodes_to_delete
		(
		id
		)

   /* temporary table that stores node status info*/
   create table #nodes_delete_info
		(
		NodeGUID char(38) not null,
		NodeState int default 0,
		NodeLevel int NULL,
		LockingUser int,
		LockingMachine nvarchar(50),
		name varchar(250) null
		)

    CREATE Index nodes_delete_info_nodeguid On #nodes_delete_info
		(
		nodeguid
		)

    /*another temporary table that serves as a stack when expanding tree hierarchy*/
   create table #node_stack
		(
		NodeLevel int not null,
		Id int not null,	
		NodeGUID char(38) not null,
		Type int not null,
		IsShortcut tinyint,
		name varchar(250) null
		);    	

    CREATE Index node_stack_level On #node_stack
		(
		NodeLevel
		)

	/*find out whether we're deleting a single node or a container*/
	set rowcount 1	
	select @Type = Type, @IsSourceNodeDeleted = DeletedByUserID 
	from node 
	where NodeGUID = @SourceNodeGUID

	if @Type = 0 
	begin
		select @SQL = 'Node with GUID ' + @SourceNodeGUID + ' does not exist in the database.'
		raiserror (@SQL, 15, 1)
		goto Cleanup
	end
	else
		if @Type != 4 and @Type != 64 and @Type != 1024 and 	/*these are all container types*/
		@Type != 16384 and @Type != 524288 and @Type != 2097152
			select @IsItem = 1	

	set rowcount 0

   /*retrieve all children of the specified node*/
	if @IsItem > 0 	/*single node case*/
   	begin
	    insert into #nodes_to_delete (Nodelevel, id, NodeGUID, Type, IsShortcut, name)
		select 0, id, NodeGUID, Type, IsShortcut, name 
		from node 
		where NodeGUID = @SourceNodeGUID
	end

	else	/*container*/

	begin	
		select @level = 1, @currParentGuid = NodeGUID, @currID = Id, @Type = Type, 
		@IsShortcut = IsShortcut, @name = name  
		from Node with (UPDLOCK)
		where NodeGUID = @SourceNodeGUID

		insert into #node_stack (NodeLevel, Id, NodeGUID, Type, IsShortcut, name ) 
		values (@level, @currID, @currParentGUID, @Type, @IsShortcut, @name )

		while (@level > 0 )	--Do if any levels remain
		   begin
			--see if there are any rows for the level we're on
			if exists( select Id from #node_stack where NodeLevel = @level )
			   begin
				--get first node at current level
				set rowcount 1
				select @currParentGUID = NodeGUID, @currID = Id, @Type = Type, 
				@IsShortcut = IsShortcut, @name = name
				from #node_stack where NodeLevel = @level
				
				set rowcount 0
	
				--insert this node into result table
				insert into #nodes_to_delete (NodeLevel, Id, NodeGUID, Type, IsShortcut, name )
					values (@level, @currID, @currParentGUID, @Type, @IsShortcut, @name )
				
				--delete this node from the stack
				delete from #node_stack 
				where NodeLevel = @level and id = @currID
	
				--get children of this node
				insert into #node_stack (NodeLevel, Id, NodeGUID, Type, IsShortcut, name )
					select @level + 1, n1.Id, n1.NodeGUID, n1.Type, n1.IsShortcut, n1.name 
					from Node n1 with (UPDLOCK)
					where parentguid = @currParentGUID 
				if @@ROWCOUNT > 0
				   select @level = @level + 1
			    end
			else
			   --there is no level below this one, so pop up one
			   select @level = @level - 1
		  end	--while (@level > 0 )
     end	--container

    /*OK, at this point we have descendants in the #nodes_to_delete table. 
    Now we want retrieve all shortcuts to the pages we're deleting */    
    insert into #nodes_to_delete (Nodelevel, id, NodeGUID, Type, IsShortcut, name)
		select nd.NodeLevel, Posting.Id, Posting.NodeGUID, Posting.Type, Posting.IsShortcut, Page.name 
		from #nodes_to_delete nd, node Posting with (UPDLOCK), node Page 
		where Posting.FollowGUID = nd.NodeGUID
		and Page.NodeGUID = Posting.FollowGUID

    /*populate delete_info table*/
    insert into #nodes_delete_info (NodeGUID, nodelevel, name) 
		select distinct nd.NodeGUID, nd.nodelevel, nd.name  
		from #nodes_to_delete nd 
		order by nd.nodelevel

	if @DeleteAction != @MOVE_FROM_RECYCLE_BIN /*if we're moving to recycle bin or purging*/
	begin

	    /*find nodes that are locked and check if the user is the one who locked them*/
	    update #nodes_delete_info 
		set nodestate = nodestate | 2,  /*USER_LOCK_FAILED*/
		LockingUser = n.LockUserId, LockingMachine = n.LockMachinename
		from #nodes_to_delete nd inner join  node n on (nd.id = n.id)
		where #nodes_delete_info.Nodeguid = n.nodeguid and n.IsLocked = 1 and 
		(n.LockUserId != @UserID or upper(n.LockMachinename) != upper(@UserMachineName))
	
	    if @@ROWCOUNT > 0 
		select @Failed = 1

	    /*if we're deleting templates make sure there are no pages or channels that depend on them*/
	    update #nodes_delete_info set nodestate = nodestate | 8  /*TEMPLATE_FAILED*/
		from NodeLayout, node 
		where #nodes_delete_info.NodeGUID = NodeLayout.LayoutTemplateGUID
		and NodeLayout.NodeID = node.ID and node.ArchiveSourceGUID is null /*do not check archived versions*/
	
	    if @@ROWCOUNT > 0 
		select @Failed = 1
	end

    /*check if user has rights to delete/undelete these nodes using the following logic:
	- currently we require a user to be an administrator in order delete/restore containers
    (this check is actually done in the server code, but we're double checking just in case)
	- if a user is purging or restoring an item (page, template, etc.) and he is not an administrator 
	then we check if he is the same user who originally deleted this item (that is moved to Recycle Bin)
	- things get really ugly when we're moving an item to Recycle Bin as at this time we have to perform 
	a horrible join to check user's rights to this item
	*/
	
	--let's hope that this guy is an administrator
	--exec CheckAdminRights @UserGroupIds, @HasRights out
	/*we used to check administrator's rights within the stored procedure, but this didn't account for local 
	administrators, so now we're passing this info in @UserGroupIDs */
	if @UserGroupIDs = '-1'
		set @HasRights = 1

	if @HasRights = 0 
	begin
		--we're dealing with a single node here
		if @IsItem > 0
			begin
				declare @everybody int
				select @everybody = userid 
				from aeuser 
				where usernameupper = 'EVERYBODY' 

				-- we're purging or restoring an item
				if @DeleteAction = @MOVE_FROM_RECYCLE_BIN or
					(@DeleteAction = @PURGE and @IsSourceNodeDeleted is not null 
					and @IsSourceNodeDeleted > 0) --the item is already in Recycle Bin
					begin
						set @SQL = 'update #nodes_delete_info set nodestate = nodestate | 4 ' +  /*RIGHTS_FAILED*/ 
						'where #nodes_delete_info.Nodeguid not in ' + 
						'(select n.NodeGUID from #nodes_to_delete nd inner join  node n on (nd.id = n.id) ' + 
						'where n.DeletedByUserId = ' + convert(varchar(10), @UserID)  +
						' or n.OwnerID IN (' + @UserGroupIDs + ') or n.ownerid = ' +
						convert(varchar(10),@everybody) + ')'

						exec sp_executesql @SQL
					
					    if @@ROWCOUNT > 0 
							select @Failed = 1
					end
				else	--moving an item into Recycle Bin or purging from WBC
					begin
						--now let's do that horrible join...
					    set @SQL = 'update #nodes_delete_info set nodestate = nodestate | 4 ' +   /*RIGHTS_FAILED*/ 
						'where NodeGUID not in (select n1.NodeGUID ' +  
						'from #nodes_to_delete nd, node n1, node NodeParent, noderole as nr, node as UserRole, ' + 
						'UserRoleNode as urn, UserRoleMember as urm ' +  
						'where nd.id = n1.id and n1.ParentGUID = NodeParent.NodeGUID and ' +  
						'NodeParent.id = nr.nodeid and nr.RoleGuid = UserRole.NodeGuid and ' + 
						'UserRole.ID = urn.NodeID and urn.NodeType = ' + 
						'case when n1.Type = 16 and n1.IsShortcut > 0 then -2147483616 else n1.Type end and ' + 
						'urn.NodeId = urm.NodeID and ' + 
						'urm.UserId IN (' + @UserGroupIDs + ') ' + 
						' AND (urn.Rights = ' + @ACL_FULL_CONTROL + 
						' or floor(urn.Rights/' + @ACL_DELETE_SIMPLE_NODE + ')%2 > 0 or ' + 
						'(floor(urn.Rights/' + @ACL_DELETE_OWN_NODE + ')%2 > 0 and ' + 
						'( n1.OwnerID IN (' + @UserGroupIDs + ') or n1.ownerid = ' + 
						convert(varchar(10),@everybody) + '))) )'

						exec sp_executesql @SQL
				    	
						if @@ROWCOUNT > 0 
							select @Failed = 1
					end
			end
		else	--containers and their children, need administrator's rights
			begin
			   update #nodes_delete_info set nodestate = nodestate | 4  /*RIGHTS_FAILED*/ 
			   select @Failed = 1
			end	
	end


   	if @Failed = 0 
	begin
		if @DeleteAction = @MOVE_TO_RECYCLE_BIN	/*moving into recycle bin*/
			begin
				select @dateCurrent = GETDATE()
				
				exec GetGMTTime @dateCurrent, @dateGMT out

				update node set DeletedByUserId = @UserID, DeletedWhen = @dateGMT ,
					ApprovalStatus = 0
					from #nodes_to_delete 
					WHERE Node.Id = #nodes_to_delete.id

				update nodelock set newApprovalStatus = 0
					from #nodes_to_delete 
					WHERE Nodelock.nodeId = #nodes_to_delete.id
				
				update node set IsCheckedOut = 1, IsCheckedOutVersion = 1, HasCheckedInVersion = 0
					from #nodes_to_delete 
					where Node.Id = #nodes_to_delete.id and 
					Node.Type in (16, 65536) and Node.IsCheckedOut = 0

				update node set ParentGUID = @NewParentGUID 
				where NodeGUID = @SourceNodeGUID
			end
		else if @DeleteAction = @MOVE_FROM_RECYCLE_BIN	/*Moving from recycle bin*/
			begin
				update node set DeletedByUserId = NULL, DeletedWhen = NULL
					from #nodes_to_delete 
					WHERE Node.Id = #nodes_to_delete.id

				update node set ParentGUID = @NewParentGUID 
				where NodeGUID = @SourceNodeGUID
			end

		else 		/*@DeleteAction = 2 - purging*/
		 	begin
			/*now it's time to delete these nodes*/
			DELETE NodeProperty FROM #nodes_to_delete WHERE NodeProperty.NodeId = #nodes_to_delete.id and #nodes_to_delete.Type != 256
			DELETE NodeRole from #nodes_to_delete WHERE NodeRole.NodeId = #nodes_to_delete.id and #nodes_to_delete.Type != 256
			DELETE NodeResource from #nodes_to_delete WHERE NodeResource.NodeId = #nodes_to_delete.id and #nodes_to_delete.Type != 256
			DELETE ControlProperty from #nodes_to_delete WHERE ControlProperty.NodeId = #nodes_to_delete.id and #nodes_to_delete.Type != 256
			DELETE NodeControl from #nodes_to_delete WHERE NodeControl.NodeId = #nodes_to_delete.id and #nodes_to_delete.Type != 256
			DELETE LayoutTemplateProperty from #nodes_to_delete WHERE LayoutTemplateProperty.NodeId = #nodes_to_delete.id and #nodes_to_delete.Type != 256
			DELETE LayoutProperty from #nodes_to_delete WHERE LayoutProperty.NodeId = #nodes_to_delete.id and #nodes_to_delete.Type != 256
			DELETE NodeLayout from #nodes_to_delete WHERE NodeLayout.NodeId = #nodes_to_delete.id and #nodes_to_delete.Type != 256
			DELETE GalleryControl from #nodes_to_delete WHERE GalleryControl.NodeId = #nodes_to_delete.id and #nodes_to_delete.Type != 256
			DELETE UserRoleMember from #nodes_to_delete WHERE UserRoleMember.NodeId = #nodes_to_delete.id and #nodes_to_delete.Type != 256
			DELETE UserRoleNode from #nodes_to_delete WHERE UserRoleNode.NodeId = #nodes_to_delete.id and #nodes_to_delete.Type != 256
			DELETE NodeLock from #nodes_to_delete WHERE NodeLock.NodeId = #nodes_to_delete.id and #nodes_to_delete.Type != 256
		
			DELETE Node from #nodes_to_delete WHERE Node.Id = #nodes_to_delete.id and #nodes_to_delete.Type != 256
		
			/*delete revisions*/
			DELETE NodeProperty where nodeid in (select node.id from node, #nodes_to_delete 
											WHERE #nodes_to_delete.NodeGUID = node.ArchiveSourceGUID
											and #nodes_to_delete.Type != 256)
			DELETE NodeRole where nodeid in (select node.id from node, #nodes_to_delete 
											WHERE #nodes_to_delete.NodeGUID = node.ArchiveSourceGUID
											and #nodes_to_delete.Type != 256)
			DELETE NodeResource where nodeid in (select node.id from node, #nodes_to_delete 
											WHERE #nodes_to_delete.NodeGUID = node.ArchiveSourceGUID
											and #nodes_to_delete.Type != 256) 
			DELETE ControlProperty where nodeid in (select node.id from node, #nodes_to_delete 
											WHERE #nodes_to_delete.NodeGUID = node.ArchiveSourceGUID
											and #nodes_to_delete.Type != 256)
			DELETE NodeControl where nodeid in (select node.id from node, #nodes_to_delete 
											WHERE #nodes_to_delete.NodeGUID = node.ArchiveSourceGUID
											and #nodes_to_delete.Type != 256)

			DELETE LayoutTemplateProperty where nodeid in (select node.id from node, #nodes_to_delete 
											WHERE #nodes_to_delete.NodeGUID = node.ArchiveSourceGUID
											and #nodes_to_delete.Type != 256)
 
			DELETE LayoutProperty where nodeid in (select node.id from node, #nodes_to_delete 
											WHERE #nodes_to_delete.NodeGUID = node.ArchiveSourceGUID
											and #nodes_to_delete.Type != 256)
			DELETE NodeLayout where nodeid in (select node.id from node, #nodes_to_delete 
											WHERE #nodes_to_delete.NodeGUID = node.ArchiveSourceGUID
											and #nodes_to_delete.Type != 256)
			DELETE GalleryControl where nodeid in (select node.id from node, #nodes_to_delete 
											WHERE #nodes_to_delete.NodeGUID = node.ArchiveSourceGUID
											and #nodes_to_delete.Type != 256)
			DELETE UserRoleMember where nodeid in (select node.id from node, #nodes_to_delete 
											WHERE #nodes_to_delete.NodeGUID = node.ArchiveSourceGUID
											and #nodes_to_delete.Type != 256)
			DELETE UserRoleNode where nodeid in (select node.id from node, #nodes_to_delete 
											WHERE #nodes_to_delete.NodeGUID = node.ArchiveSourceGUID
											and #nodes_to_delete.Type != 256) 

			DELETE NodeLock 
			from #nodes_to_delete, Node 
			WHERE #nodes_to_delete.NodeGUID = Node.ArchiveSourceGUID
			and NodeLock.NodeId = Node.id and #nodes_to_delete.Type != 256

			DELETE Node 
			FROM #Nodes_to_delete 
			WHERE ArchiveSourceGUID = #nodes_to_delete.NodeGUID and #nodes_to_delete.Type != 256
			
			select @dateCurrent = GETDATE()
			exec GetGMTTime @dateCurrent, @dateGMT out
	
			--move resource galery items to Archive Folder
			update node set ParentGUID = @ArchiveFolderGUID,
			ArchivedWhen = @dateGMT,
			IsLocked = 0	
			from #nodes_to_delete
			where #nodes_to_delete.Id = Node.Id and #nodes_to_delete.Type = 256

			--clear the lock on resource galery items 
			DELETE NodeLock 
			from #nodes_to_delete, Node 
			where #nodes_to_delete.Id = Node.Id and #nodes_to_delete.Type = 256 and NodeLock.NodeId = Node.Id
			end			
			
	    /*return node status into*/	
    	exec sp_executesql N'select ndi.NodeGUID, ndi.NodeState, nd.Type, nd.IsShortcut, nd.name,
		ndi.LockingUser, ndi.LockingMachine, #Sandbox.LastChangeTime
		from (#nodes_delete_info ndi left join 
		(select distinct nodeguid, Type, IsShortcut, name from #nodes_to_delete) as nd 
		on ndi.nodeguid = nd.nodeguid) left join #Sandbox on ndi.NodeGUID = #Sandbox.NodeGUID'
	end
	else
	    /*return node status into*/	
    	exec sp_executesql N'select distinct ndi.NodeGUID, ndi.NodeState, n.Type, n.IsShortcut, ndi.name,
		ndi.LockingUser, ndi.LockingMachine, max(n.LastChangeTime) as LastChangeTime
		from #nodes_delete_info ndi left join node as n
		on ndi.nodeguid = n.nodeguid 
		where ndi.NodeState > 0
		group by ndi.NodeGUID, ndi.NodeState, n.Type, n.IsShortcut, ndi.name,
		ndi.LockingUser, ndi.LockingMachine'


Cleanup:

   /*we're done, clean up temp tables*/
   drop table #nodes_to_delete
   drop table #nodes_delete_info
   drop table #node_stack

GO

drop procedure GetUserIDWithReplace
drop procedure CheckNodeForUpdate

GO
